package org.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.data.SportsmanProfile;

/**
 *
 * @author George.Mavchun
 */
public class SportsmenDAO {
    public static final SportsmanProfile EMPTY_SPORTSMAN_PROFILE = new SportsmanProfile();

    /** Add sportsman profile to database. If id not presented in SportsmanProfile use the first available.
     *
     *
     * @param sp sporsmen profile that should be added to DB
     *
     * @see GeneralDAO.getAvailableId()
     */
    public static void addSportsman(SportsmanProfile sp) {
        if (sp.id == null) {
            sp.id = GeneralDAO.getAvailableId(new SportsmanProfile());
        }
        sp.password = Util.getMD5Hash(sp.password);
        GeneralDAO.getDb().insert(sp);
    }
    
    /**
     * Check if user with this parameters exists in database and return his id.
     *
     * @param email email of user
     * @param password password of user
     *
     * @return id of user of -1 if user with this email and password not exist
     */ 
    public static Integer readLoginParams(String email, String password) {
        SportsmanProfile sp = new SportsmanProfile();
        List<Integer> list = GeneralDAO.getDb().from(sp).where(sp.email).is(email).and(sp.password).is(Util.getMD5Hash(password)).select(sp.id);
        if (!list.isEmpty()) {
            for (Integer id : list) {
                return id;
            }
        }
        return -1;
    }
    
    /** Read from DB and return SportsmanProfile by its id
     * 
     * @param id id of profile which should be returned
     *
     * @return Sportsman profile by id
     */
    public static SportsmanProfile getSportsmanProfile(Integer id) {
        SportsmanProfile sp = new SportsmanProfile();
        List<SportsmanProfile> list = GeneralDAO.getDb().from(sp).where(sp.id).is(id).select();
        if (!list.isEmpty()) {
            return list.get(list.size() - 1);
        }
        return null;
    }
    
    /** Read from DB and return SportsmanProfile by its email
     * 
     * @param email email of profile which should be returned
     * @return Sportsman profile with presented email
     */
    public static SportsmanProfile getSportsmanProfile(String email) {
        SportsmanProfile sp = new SportsmanProfile();
        List<SportsmanProfile> list = GeneralDAO.getDb().from(sp).where(sp.email).is(email).select();
        if (!list.isEmpty()) {
            return list.get(list.size() - 1);
        }
        return null;
    }
    /** Modifies profile by deleting old and inserting new.
     *
     *
     *
     * @param new_sp new profile
     */
    public static void modifySportsmanProfile(SportsmanProfile new_sp) {
        SportsmanProfile sp = new SportsmanProfile();
        List<SportsmanProfile> list = GeneralDAO.getDb().from(sp).where(sp.id).is(new_sp.id).select();
        if (!list.isEmpty()) {
            for (SportsmanProfile temp : list) {
                GeneralDAO.getDb().from(sp).where(sp.id).is(temp.id).delete();
            }
        }
        GeneralDAO.getDb().insert(new_sp);
    }

    /** Change password of profile
     *
     * @param id if of profile which password should be changed
     * @param newPassword new password of profile
     */
    public static void changePassword(Integer id, String newPassword) {
        String md5password = "";
        md5password = Util.getMD5Hash(newPassword);
        SportsmanProfile sp  = new SportsmanProfile();
        SportsmanProfile temp = new SportsmanProfile();
        List<SportsmanProfile> list = GeneralDAO.getDb().from(sp).where(sp.id).is(id).select();
        if (!list.isEmpty()) {
            temp = list.get(list.size() - 1);
            GeneralDAO.getDb().from(sp).where(sp.id).is(id).delete();
        }
        temp.password = "".equals(md5password) ? temp.password : md5password;
        GeneralDAO.getDb().insert(temp);  
    }
    /** Set flag of deleting profile.
     *
     *
     * @param id id of profile which should be deleted
     */
    public static void deleteSportsmanProfile(Integer id) {
        SportsmanProfile sp = new SportsmanProfile();
        List<SportsmanProfile> list = GeneralDAO.getDb().from(sp).where(sp.id).is(id).select();
        if (!list.isEmpty()) {
            for(SportsmanProfile temp : list) {
                temp.deleted = 1;
                GeneralDAO.getDb().from(sp).where(sp.id).is(id).delete();
                GeneralDAO.getDb().insert(temp);
            }
        }
    }

    /** Check if email address is already in database
     * 
     * @param email email that should be checked
     * @return true if email is in DB and false if no
     */
    public static boolean isEmailInDatabase(String email) {
        SportsmanProfile sp = new SportsmanProfile();
        long count = GeneralDAO.getDb().from(sp).where(sp.email).is(email).selectCount();
        if (count > 0) {
            return true;
        }
        return false;
    }
    /** Returns all profiles preseneted in DB
     *
     * @return all profiles presented in DB
     */
    public static List getSportsmanProfiles() {
        SportsmanProfile sp = new SportsmanProfile();
        return GeneralDAO.getDb().from(sp).orderBy(sp.id).select();
    }

    public static List<SportsmanProfile> getAllProfilesPartial(int count, int startNumber, String sortColumn, String sortType) {
        Connection connection = GeneralDAO.getConnection();
        List<SportsmanProfile> result = new ArrayList<SportsmanProfile>();
        String sql = "SELECT * FROM SPORTSMANPROFILE ORDER BY " + sortColumn + " " + sortType + " LIMIT " + count + " OFFSET " + startNumber;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String email = resultSet.getString(2);
                String password = resultSet.getString(3);
                String name = resultSet.getString(4);
                String surname = resultSet.getString(5);
                Timestamp birthday = resultSet.getTimestamp(6);
                String club = resultSet.getString(7);
                String qual = resultSet.getString(8);
                String sex = resultSet.getString(9);
                String trainer = resultSet.getString(10);
                String city = resultSet.getString(11);
                int deleted = resultSet.getInt(12);

                SportsmanProfile profile = new SportsmanProfile(id, email, password, name, surname, birthday, club, qual, sex, trainer, city);
                profile.deleted = deleted;
                result.add(profile);
            }
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public static void modifySportsmanProfileField(Integer id, String field, String value) {
        Connection connection = GeneralDAO.getConnection();
        String sql = "UPDATE SPORTSMANPROFILE SET " + field + "=? WHERE id=?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, value);
            statement.setInt(2, id);
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

